package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;
import java.util.Vector;

import javax.naming.NamingException;
import javax.servlet.http.HttpSession;
import javax.sql.rowset.CachedRowSet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.DeleteForm;
import forms.ProcessNotStartedInsertForm;
import forms.StudentFormUpdate;

public class Database {


	private static String insertStr = "INSERT INTO users (user_name, password) VALUES (?, ?)";
	private static String updateStr = "UPDATE students SET first_name = ?, "
			+ "middle_name = ?, last_name = ? WHERE id = ?";
	private static String deleteStr = "DELETE FROM students WHERE id = ?";
	
	public static void insertReviewer(ProcessNotStartedInsertForm reviewer) throws DbException, ClassNotFoundException, SQLException {

		
			Class.forName("org.postgresql.Driver");

			String database = "postgres";
			String username = "postgres";
			String password = "postgres";

			Connection conn = DriverManager.getConnection("jdbs:postgresql:"+ database, username, password);

			// Create the prepared statement and use it to
			// INSERT student values INTO the Students table.
			PreparedStatement pStmt = conn.prepareStatement("INSERT INTO users (user_name, \"password\", email) VALUES (?, md5(?), ?);");

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setString(2, reviewer.getPassword());
			pStmt.setString(3, reviewer.getEmail());
			pStmt.execute();
			
			int id = Database.getIdByUsername(reviewer.getUsername());
			
			pStmt = conn.prepareStatement("INSERT INTO user_roles (role, user_name, user_ref) VALUES ('reviewer', ?, ?)");

			pStmt.setString(1, reviewer.getUsername());
			pStmt.setInt(2, id);
			pStmt.execute();
			
			// Commit transaction
			conn.commit();
			pStmt.close();
			conn.close();
	}
	

	public static void deleteReviewer(String reviewerUsername) throws DbException, SQLException, ClassNotFoundException {

			Class.forName("org.postgresql.Driver");

			String database = "postgres";
			String username = "postgres";
			String password = "postgres";

			Connection conn = DriverManager.getConnection("jdbs:postgresql:"+ database, username, password);
			PreparedStatement pStmt = conn.prepareStatement("DELETE FROM user_roles WHERE user_name = ?", ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			pStmt.setString(1, reviewerUsername);
			pStmt.execute();
			
			// Create the prepared statement and use it to
			// DELETE students FROM the Students table.
			 pStmt = conn.prepareStatement("DELETE FROM users WHERE user_name = ?", ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);

			pStmt.setString(1, reviewerUsername);
			pStmt.execute();
	
	}

	// END STUDENT DATABASE COPY METHODS
	
	
	public static boolean createAccount(String user, String pass,
			String email) throws SQLException, ClassNotFoundException {
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:"+ database, username, password);
		PreparedStatement stmt;
		
		  stmt = conn.prepareStatement(
		  "INSERT INTO users(user_name, \"password\", email) VALUES (?, md5(?), ?);"
		  );
		  stmt.setString(1, user);
		  stmt.setString(2, pass);
		  stmt.setString(3, email);
		  stmt.execute();
		  
		  stmt = conn.prepareStatement("INSERT INTO user_roles(user_ref, user_name, \"role\") VALUES ( ?, ?, ?);");
		  stmt.setInt(1, Database.getIdByUsername(user));
		  stmt.setString(2,user);
		  stmt.setString(3, "student");
		  stmt.execute();
		 
		/* 
		 * if(!(conf_password.equals("")||password.equals(""))){
		 * if(!conf_password.equals(password)){
		 * response.sendRedirect("register.jsp?pass=true"); } } else{
		 * <jsp:forward page ="register.jsp?empty=true"/> } stmt = conn
		 * .prepareStatement("SELECT max(user_id)FROM users;"); rs =
		 * stmt.executeQuery(); rs.next(); int id = rs.getInt(1);
		 * 
		 * try { stmt = conn .prepareStatement(
		 * "INSERT INTO user_roles( user_ref, user_name, \"role\") VALUES (?, ?, ?);"
		 * ); stmt.setInt(1, id); stmt.setString(2, user_name);
		 * stmt.setString(3, "student"); stmt.execute();
		 * 
		 * } catch (SQLException e) { e.printStackTrace(); }
		 */
		 
		return true;
	}
	
	public static CachedRowSet getAllReviewers() throws DbException, ClassNotFoundException, SQLException {

			Class.forName("org.postgresql.Driver");

			String database = "postgres";
			String username = "postgres";
			String password = "postgres";

			Connection conn = DriverManager.getConnection("jdbs:postgresql:"+ database, username, password);
			PreparedStatement pStmt = conn.prepareStatement("SELECT user_roles.user_name, user_roles.user_ref " +
					"FROM public.user_roles WHERE user_roles.\"role\" = 'reviewer';", ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			//PreparedStatement pStmt = conn.prepareStatement("SELECT user_name FROM user_roles;");

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allReviewers = pStmt.executeQuery();

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allReviewers);

			return crsReviewers;
	}
	
	
	public static boolean checkForTwoReviewers() throws SQLException, ClassNotFoundException {

		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);

		PreparedStatement stmt = conn.prepareStatement("SELECT user_roles.\"role\" FROM public.user_roles WHERE user_roles.\"role\" = 'reviewer';", ResultSet.TYPE_SCROLL_INSENSITIVE,
				ResultSet.CONCUR_READ_ONLY);
		
		ResultSet rset = stmt.executeQuery();
		rset.last();
		int reviewerCount = rset.getRow();
		
		if(reviewerCount <= 1) {
			return false;
		}

		return true;
	}
	
	public static String getAppStatus(int id) throws SQLException, ClassNotFoundException{
		
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:"+ database, username, password);
		PreparedStatement stmt;
		
		  stmt = conn.prepareStatement("SELECT app_status.status FROM public.app_status WHERE app_status.id = ?;");
		  stmt.setInt(1, id);
		  
		  ResultSet rs = stmt.executeQuery();
		  rs.next();
		  String appStatus = rs.getString(1);

		return appStatus;
		//TODO
	}
	
	public static String printDegrees(Vector<Degree> degreesVector) throws SQLException, ClassNotFoundException {
		String degreeString = "";
		
		if (degreesVector.size() > 0) {
			for (int degree_count = 0; degree_count < degreesVector.size(); degree_count++) {
				Degree degree = (Degree) degreesVector.get(degree_count);
				
				degreeString = degreeString + "<br /> <strong> Degree Number: " + (degree_count+1) + "<br /></strong>";
				degreeString = degreeString + "<br /> Degree-Country: " + degree.getCountry();
				if(degree.hasState())
					degreeString = degreeString + "<br /> Degree-State: " + degree.getState();
				degreeString = degreeString + "<br /> Degree-University: " + degree.getUniversity();
				degreeString = degreeString + "<br /> Degree-Discipline: " + degree.getDiscipline();
				degreeString = degreeString + "<br /> Degree-Title: " + degree.getTitle();
				degreeString = degreeString + "<br /> Degree-GPA: " + degree.getGpa();
				degreeString = degreeString + "<br /> Degree-Date: " + degree.getDate();		
				degreeString = degreeString + "<br />";
			}
		return degreeString;
		}
	return "";
}
	
	
	
	public static int[] getDisciplineAnalytics() throws SQLException, ClassNotFoundException{
		
	
		Vector<String> disciplinesVector = Database.getDisciplines();
		int[] num_discipline = new int[disciplinesVector.size()];

		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:"
				+ database, username, password);

		for (int i = 1; i <= disciplinesVector.size(); i++) {
			// get country
			// creates statement to query
			PreparedStatement stmt = conn
					.prepareStatement("SELECT degree.discipline, count(distinct degree.owner) FROM public.degree, public.discipline WHERE degree.discipline = ? AND degree.discipline = discipline.id GROUP BY degree.discipline;");
			stmt.setInt(1, i);

			try {
				ResultSet rs = stmt.executeQuery();
				if (rs.next()) {
					num_discipline[i-1] = rs.getInt(2);
				} else {
					num_discipline[i-1] = 0;
				}
			} catch (SQLException e) {
				System.out.println("fail");
			}
		}
		return num_discipline;
	}

	public static int[] getSpecializationAnalytics() throws SQLException,
			ClassNotFoundException {
		Vector<String> specializationsVector = Database.getSpecializations();

		int[] num_special = new int[specializationsVector.size()];

		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:"
				+ database, username, password);

		for (int i = 0; i < specializationsVector.size(); i++) {   // WHY IS FOR LOOP STARTING AT ONE?
			// get country
			// creates statement to query
			PreparedStatement stmt = conn
					.prepareStatement("SELECT applicant.specialization, count(*) FROM public.applicant, public.specialization WHERE applicant.specialization = ? AND applicant.specialization = specialization.id GROUP BY applicant.specialization;");
			stmt.setInt(1, i+1);

			try {
				ResultSet rs = stmt.executeQuery();
				if (rs.next()) {
					num_special[i] = rs.getInt(2);
				} else {
					num_special[i] = 0;
				}
			} catch (SQLException e) {
				System.out.println("fail");
			}
			stmt.close();

		}

		return num_special;
	}

	/**
	 * getStates
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Vector<String> getStates() throws SQLException,
			ClassNotFoundException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);
		// select what you want from the database
		PreparedStatement stmt = conn
				.prepareStatement(
						"SELECT \"universityLocations\".\"name\" FROM public.\"universityLocations\" WHERE \"universityLocations\".id < 51;",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
		ResultSet rset = stmt.executeQuery();
		int count = 0;

		while (rset.next()) {
			count++;
		}

		rset.first();
		Vector<String> v = new Vector<String>();

		for (int k = 0; k < count; k++) {
			v.add(k, rset.getString(1));
			rset.next();
		}

		return v;
	}

	/**
	 * getSpecializations
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Vector<String> getSpecializations() throws SQLException,
			ClassNotFoundException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);
		// select what you want from the database
		PreparedStatement stmt = conn.prepareStatement(
				"SELECT name FROM specialization;",
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ResultSet rset = stmt.executeQuery();
		int count = 0;

		while (rset.next()) {
			count++;
		}

		rset.first();
		Vector<String> v = new Vector<String>();

		for (int k = 0; k < count; k++) {
			v.add(k, rset.getString(1));
			rset.next();
		}

		return v;
	}

	/**
	 * isUniqueUsername
	 * 
	 * @param user
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static boolean isUniqueUsername(String user) throws SQLException,
			ClassNotFoundException {

		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String dbpassword = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:"
				+ database, username, dbpassword);

		PreparedStatement stmt = conn
				.prepareStatement("SELECT users.user_name FROM public.users WHERE users.user_name = ?;");
		stmt.setString(1, user);
		ResultSet rs = stmt.executeQuery();

		rs.next();
		try{
			rs.getString(1);
		}catch(SQLException e){
			return true;
		}

		return true;
	}

	/**
	 * isUniqueEmail
	 * 
	 * @param email
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static boolean isUniqueEmail(String email) throws SQLException,
			ClassNotFoundException {

		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String dbpassword = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:"
				+ database, username, dbpassword);

		PreparedStatement stmt = conn
				.prepareStatement("SELECT users.email FROM public.users WHERE users.email = ?;");

		stmt.setString(1, email);
		ResultSet rs = stmt.executeQuery();

		rs.next();
			try{
				rs.getString(1);
			}catch(SQLException e){
				return true;
			}

			return true;
	}

	/**
	 * Get Disciplines
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public static Vector<String> getDisciplines() throws SQLException,
			ClassNotFoundException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);
		// select what you want from the database
		PreparedStatement stmt = conn.prepareStatement(
				"SELECT name FROM discipline;",
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ResultSet rset = stmt.executeQuery();
		int count = 0;

		while (rset.next()) {
			count++;
		}

		rset.first();
		Vector<String> v = new Vector<String>();

		for (int k = 0; k < count; k++) {
			v.add(k, rset.getString(1));
			rset.next();
		}

		return v;
	}

	/**
	 * getLocations
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Vector<String> getLocations() throws ClassNotFoundException,
			SQLException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);
		// select what you want from the database
		PreparedStatement stmt = conn.prepareStatement(
				"SELECT \"name\" FROM \"universityLocations\";",
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

		ResultSet rset = stmt.executeQuery();

		int count = 0;

		while (rset.next()) {
			count++;
		}

		rset.first();
		Vector<String> v = new Vector<String>();

		for (int k = 0; k < count; k++) {
			v.add(k, rset.getString(1));
			rset.next();
		}

		return v;
	}

	/**
	 * getUniversitiesAtLocation
	 * 
	 * @param location
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Vector<String> getUniversitiesAtLocation(String location)
			throws ClassNotFoundException, SQLException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);
		// select what you want from the database
		PreparedStatement stmt = conn
				.prepareStatement(
						"SELECT \"universityLocations\".id "
								+ "FROM public.\"universityLocations\" WHERE \"universityLocations\".\"name\" = ?;",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
		stmt.setString(1, location);
		ResultSet rset = stmt.executeQuery();
		rset.next(); // go to first row
		int universityKey = rset.getInt(1);

		stmt = conn
				.prepareStatement(
						"SELECT university.\"name\" FROM public.university WHERE university.\"location\" = ?;",
						ResultSet.TYPE_SCROLL_INSENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
		stmt.setInt(1, universityKey);
		rset = stmt.executeQuery();

		rset.last();
		int count = rset.getRow();
		rset.first();

		Vector<String> v = new Vector<String>();
		if (count == 0) {
			String noUniversities = "This country has no Listed Universities, please input your university";
			v.add(0, noUniversities);
			return v;
		}

		for (int k = 0; k < count; k++) {
			v.add(k, rset.getString(1));
			rset.next();
		}

		return v;
	}

	public static int getIdByUsername(String user) throws SQLException,
			ClassNotFoundException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);

		PreparedStatement stmt = conn
				.prepareStatement("SELECT users.user_id FROM public.users WHERE users.user_name = ?;");
		stmt.setString(1, user);
		// return the security question associated with that username's account
		try {
			ResultSet rs = stmt.executeQuery();
			rs.next();
			int result = rs.getInt(1);

			// Checks for null query
			if (!(result == 0)) {

				return result;
			}
		} catch (SQLException e) {
			System.err.print(e);
			System.out.println(": Username Invalid");

			// if there was an error return null
			return 0;
		}
		// since result query was null return null
		return 0;
	}

	/**
	 * getCountries
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static Vector<String> getCountries() throws ClassNotFoundException,
			SQLException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);
		// select what you want from the database
		PreparedStatement stmt = conn.prepareStatement(
				"SELECT name FROM countries;",
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ResultSet rset = stmt.executeQuery();
		int count = 0;

		while (rset.next()) {
			count++;
		}

		rset.first();
		Vector<String> v = new Vector<String>();

		for (int k = 0; k < count; k++) {
			v.add(k, rset.getString(1));
			rset.next();
		}

		return v;
	}

	/**
	 * getDegrees
	 * 
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */

	public static Vector<Degree> getDegrees(int id) throws ClassNotFoundException,
			SQLException {

		Class.forName("org.postgresql.Driver");
		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		// make a connection to the database itself
		Connection conn = DriverManager.getConnection("jdbc:postgresql:"
				+ database, username, password);
		// select what you want from the database
		PreparedStatement stmt = conn.prepareStatement(
				"SELECT countries.\"name\", degree_title.\"name\", discipline.\"name\", university.\"name\", degree.gpa, degree.date "
				+"FROM public.applicant, public.countries, public.degree, public.degree_title, public.discipline, public.university "
				+"WHERE applicant.id = ? AND degree.\"owner\" = applicant.pid AND degree.country = countries.id AND degree.discipline = discipline.id AND degree.degree_title = degree_title.id AND degree.university = university.id;");

		stmt.setInt(1, id);
		ResultSet rset = stmt.executeQuery();

		Vector<Degree> v = new Vector<Degree>();
		while(rset.next()){
			Degree degree = new Degree();
			String country = rset.getString(1);
			String title = rset.getString(2);
			String discipline = rset.getString(3);
			String university = rset.getString(4);
			Double gpa = rset.getDouble(5);
			String date = rset.getString(6);
			if(country.equals("United States")){
				stmt = conn.prepareStatement("SELECT state.\"name\" FROM public.degree, public.state, public.applicant WHERE degree.\"owner\" = applicant.pid AND degree.state = state.id AND applicant.id = ?;");
				stmt.setInt(1, id);
				rset = stmt.executeQuery();
				rset.next();
				String state = rset.getString(1);
				degree.setState(state);
			}
			degree.setGpa(gpa.toString());
			degree.setTitle(title);
			degree.setDiscipline(discipline);
			degree.setCountry(country);
			degree.setUniversity(university);
			degree.setDate(date);
			v.add(degree);	
		}
		/*
		 * rset.first(); Vector<String> v = new Vector<String>();
		 * 
		 * for(int k=0; k<count; k++) { v.add(k, rset.getString(1));
		 * rset.next(); }
		 * 
		 * TODO
		 */
		//Vector v = null;
		return v;
	}

	/**
	 * isValidPassword
	 * 
	 * @param user
	 * @param pass
	 * @return
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean isValidPassword(String user, String pass)
			throws ClassNotFoundException, SQLException {
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";

		Connection conn = DriverManager.getConnection("jdbs:postgresql:"
				+ database, username, password);
		PreparedStatement stmt;
		stmt = conn
				.prepareStatement("SELECT users.user_name, users.\"password\" FROM public.users WHERE users.user_name = ?;");
		stmt.setString(1, user);
		ResultSet rs = stmt.executeQuery();
		rs.next();

		String password_temp = rs.getString(2);

		if (password_temp == pass)
			return true;

		return false;
	}

	/**
	 * submit
	 * 
	 * @param applicant
	 * @param degreeVector
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 * @throws DbException 
	 */
	public static int submit(Applicant applicant, Vector<Degree> degreeVector)
			throws SQLException, ClassNotFoundException, DbException {
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		int country_of_residence = 0;
		int state = 0;
		int specialization = 0;
		int citizenship = 0;
		int residence = 0;
		int pid = 0;
		int university = 0;
		int degree_title = 0;
		int discipline = 0;
		int country = 0;
		int id = 0;

		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);

		// get country
		// creates statement to query
		PreparedStatement stmt = conn.prepareStatement("SELECT countries.id FROM public.countries WHERE countries.name = ?;");
		stmt.setString(1, applicant.getCountry_of_residence());

		// catches result
		try {
			ResultSet rs = stmt.executeQuery();
			rs.next();
			country_of_residence = rs.getInt(1);
			System.out.println(country_of_residence);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		// get state
		if (applicant.hasState()) {
			// creates statement to query
			stmt = conn.prepareStatement("SELECT state.id FROM public.state WHERE state.\"name\" = ?");
			stmt.setString(1, applicant.getState());
			// catches result
			try {
				ResultSet rs = stmt.executeQuery();
				rs.next();
				state = rs.getInt(1);
			} catch (SQLException e) {
				System.out.println("fail");
			}
		}

		//insert residence
		stmt = conn.prepareStatement("INSERT INTO residence(address, city, phone_number, country, us_res, state, zip, area_code, country_code) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);");
		stmt.setString(1, applicant.getStreet());
		stmt.setString(2, applicant.getCity());
		stmt.setString(3, applicant.getPhone_number());
		stmt.setInt(4, country_of_residence);
		stmt.setBoolean(5, applicant.isDomestic());
		if (applicant.hasState()) {
			stmt.setInt(6, state);
			stmt.setNull(9, java.sql.Types.NULL);
		} else {
			stmt.setNull(6, java.sql.Types.NULL);
			stmt.setInt(9, Integer.parseInt(applicant.getCountry_phone_code()));
		}
		stmt.setInt(7, Integer.parseInt(applicant.getZip_code()));
		stmt.setInt(8, Integer.parseInt(applicant.getArea_code()));

		try {
			stmt.executeUpdate();
		} catch (SQLException e) {
			// System.out.println("fail");
		}

		//get specialization
		stmt = conn.prepareStatement("SELECT specialization.id FROM public.specialization WHERE specialization.name = ?");
		stmt.setString(1, applicant.getSpecialization());

		try {
			stmt.executeQuery();
			ResultSet rs = stmt.executeQuery();
			rs.next();
			specialization = rs.getInt(1);
		} catch (SQLException e) {
			System.out.println("fail");
		}

		//get citizenship
		stmt = conn.prepareStatement("SELECT countries.id FROM public.countries WHERE countries.name = ?");
		stmt.setString(1, applicant.getCitizenship());
		try {
			stmt.executeQuery();
			ResultSet rs = stmt.executeQuery();
			rs.next();
			citizenship = rs.getInt(1);
		} catch (SQLException e) {
			System.out.println("fail");
		}

		//get country of residence
		stmt = conn.prepareStatement("SELECT residence.id FROM public.residence WHERE residence.address = ? AND residence.phone_number = ?");
		stmt.setString(1, applicant.getStreet());
		stmt.setString(2, applicant.getPhone_number());

		try {
			stmt.executeQuery();
			ResultSet rs = stmt.executeQuery();
			rs.next();
			residence = rs.getInt(1);
		} catch (SQLException e) {
			System.out.println("fail");
		}

		//get pid
		stmt = conn.prepareStatement("SELECT max(id) FROM applicant;");

		try {
			stmt.executeQuery();
			ResultSet rs = stmt.executeQuery();
			rs.next();
			pid = rs.getInt(1);
		} catch (SQLException e) {
			System.out.println("fail");
		}

		//insert applicant
		stmt = conn.prepareStatement("INSERT INTO applicant(first_name, last_name, residence, middle_ini, origin_country, specialization, pid, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
		stmt.setString(1, applicant.getFirst_name());
		stmt.setString(2, applicant.getLast_name());
		stmt.setInt(3, residence);
		stmt.setString(4, (String) applicant.getMiddle_initial());
		stmt.setInt(5, citizenship);
		stmt.setInt(6, specialization);
		stmt.setInt(7, pid);
		stmt.setInt(8, applicant.getUserId());
		try {
			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("fail");
		}
		int i = 0;
		while (i < degreeVector.size()) {
			Degree degree = (Degree) degreeVector.get(i);
			stmt = conn.prepareStatement("SELECT university.id	FROM public.university WHERE university.name = ?;");
			stmt.setString(1, degree.getUniversity());

			try {
				stmt.executeQuery();
				ResultSet rs = stmt.executeQuery();
				rs.next();
				university = rs.getInt(1);
			} catch (SQLException e) {
				System.out.println("fail");
			}

			stmt = conn.prepareStatement("SELECT degree_title.id From public.degree_title WHERE degree_title.name = ?;");
			stmt.setString(1, degree.getTitle());
			try {
				stmt.executeQuery();
				ResultSet rs = stmt.executeQuery();
				rs.next();
				degree_title = rs.getInt(1);
			} catch (SQLException e) {
				System.out.println("fail");
			}

			stmt = conn.prepareStatement("SELECT discipline.id From public.discipline WHERE discipline.name = ?;");
			stmt.setString(1, degree.getDiscipline());
			try {
				stmt.executeQuery();
				ResultSet rs = stmt.executeQuery();
				rs.next();
				discipline = rs.getInt(1);
			} catch (SQLException e) {
				System.out.println("fail");
			}

			stmt = conn.prepareStatement("SELECT countries.id From public.countries WHERE countries.name = ?;");
			stmt.setString(1, degree.getCountry());
			try {
				stmt.executeQuery();
				ResultSet rs = stmt.executeQuery();
				rs.next();
				country = rs.getInt(1);
			} catch (SQLException e) {
				System.out.println("fail");
			}

			if (degree.hasState()) {
				stmt = conn.prepareStatement("SELECT state.id From public.state WHERE state.name = ?;");
				stmt.setString(1, degree.getState());
				try {
					stmt.executeQuery();
					ResultSet rs = stmt.executeQuery();
					rs.next();
					state = rs.getInt(1);
				} catch (SQLException e) {
					System.out.println("fail");
				}
			}

			stmt = conn.prepareStatement("INSERT INTO degree(country, state, discipline, degree_title, \"owner\", university, gpa, date) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
			stmt.setInt(1, country);
			if (degree.hasState()) {
				stmt.setInt(2, state);
			} else {
				stmt.setNull(2, java.sql.Types.NULL);
			}
			stmt.setInt(3, discipline);
			stmt.setInt(4, degree_title);
			stmt.setInt(5, pid);
			stmt.setInt(6, university);
			stmt.setDouble(7, Double.parseDouble(degree.getGpa()));
			stmt.setString(8, degree.getDate());

			try {
				stmt.executeUpdate();
			} catch (SQLException e) {
				System.out.println("fail");
			}
			i++;
		}
		
		//insert into app_status
		stmt = conn.prepareStatement("INSERT INTO app_status(id, status) VALUES (?, ?);");
		stmt.setInt(1, applicant.getUserId());
		stmt.setString(2, "Pending");
		stmt.execute();
		
		
		//insert into review application
		stmt = conn.prepareStatement("INSERT INTO review_applications( id, \"appStatus\", comments, grade, reviewer) VALUES (?, ?, ?, ?, ?);");
		stmt.setInt(1, applicant.getUserId());
		stmt.setInt(2, applicant.getUserId());
		stmt.setString(3, "No Comment");
		stmt.setInt(4, -1);
		stmt.setInt(5, java.sql.Types.NULL);
		stmt.execute();
		return pid;

	}

	public static Applicant getApplicantById(int id) throws SQLException, ClassNotFoundException {
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		String state = null;
		String specialization = null;
		int pid = 0;
		String resCountry = null;
		//int id = 0;

		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
		PreparedStatement stmt = conn.prepareStatement("SELECT residence.address," 
				+"residence.city, residence.phone_number, residence.state, residence.zip, residence.area_code, residence.country_code, specialization.\"name\", applicant.first_name, " 
				+"applicant.last_name, applicant.middle_ini, applicant.pid, countries.\"name\" FROM public.residence, public.specialization, public.applicant, public.countries WHERE applicant.id = ? AND " 
				+"specialization.id = applicant.specialization AND residence.id = applicant.residence AND countries.id = residence.country;");
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		rs.next();
		String address = rs.getString(1);
		String city = rs.getString(2);
		String phone_number = rs.getString(3);
		int stateInt = rs.getInt(4);
		int zip = rs.getInt(5);
		int area = rs.getInt(6);
		int countryCode = rs.getInt(7);
		specialization = rs.getString(8);
		String firstName = rs.getString(9);
		String lastName = rs.getString(10);
		String middileInit = rs.getString(11);
		pid = rs.getInt(12);
		resCountry = rs.getString(13);
		
		conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
		if(stateInt != 0){
			stmt = conn.prepareStatement("SELECT state.\"name\" FROM public.state WHERE state.id = ?;");
			stmt.setInt(1, stateInt);
			rs = stmt.executeQuery();
			rs.next();
			state = rs.getString(1);
		}else{
			state = null;
		}
		
		stmt = conn.prepareStatement("SELECT countries.\"name\" FROM public.applicant, public.countries WHERE applicant.id = ? AND countries.id = applicant.origin_country;");
		stmt.setInt(1, id);
		rs = stmt.executeQuery();
		rs.next();
		
		String origCountry = rs.getString(1);
		
		Applicant applicant = new Applicant();
		applicant.setAddress(address, city, state, ((Integer)zip).toString(), ((Integer)countryCode).toString(), ((Integer)area).toString(), phone_number);
		applicant.setState(state);
		applicant.setName(firstName, middileInit, lastName);
		applicant.setCitizenship(origCountry);
		applicant.setCountry_of_residence(resCountry);
		if(resCountry.equals("United States")){
			applicant.setDomestic(true);
			applicant.setDomestic("Domestic Applicant");
		}else{
			applicant.setDomestic(false);
			applicant.setDomestic("International Applicant");
		}
		applicant.setSpecialization(specialization);
		applicant.setPID(pid);
		return applicant;
		

		// get country
		// creates statement to query
		//PreparedStatement stmt = conn.prepareStatement(
	}
	
	public static boolean startReviewProcess() throws DbException, ClassNotFoundException, SQLException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		CachedRowSet reviewers = Database.getAllReviewers();
		ArrayList<Integer> reviewerArray = new ArrayList<Integer>();
		while(reviewers.next()){
			reviewerArray.add(reviewers.getInt("user_ref"));
		}
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM public.review_applications;", ResultSet.CONCUR_UPDATABLE, ResultSet.TYPE_SCROLL_SENSITIVE);
		ResultSet rs = stmt.executeQuery();
		int reviewer;
		int i = 0;
		while(rs.next()){
			if(!(i<reviewerArray.size()))
				i = 0;
			reviewer = reviewerArray.get(i);
			stmt = conn.prepareStatement("UPDATE review_applications SET reviewer=? WHERE id = ?;");
			stmt.setInt(1, reviewer);
			stmt.setInt(2, rs.getInt("id"));
			stmt.execute();
			i++;
		}
		
		return true;
	}
	
	public static CachedRowSet getAllNotGraded() throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM public.review_applications WHERE review_applications.grade = -1;");
		
		ResultSet rs = stmt.executeQuery();
		CachedRowSet allNotGraded = new CachedRowSetImpl();
		allNotGraded.populate(rs);
		
		return allNotGraded;
	}
	
	public static CachedRowSet getAllGraded() throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM public.review_applications WHERE review_applications.grade != -1;");
		
		ResultSet rs = stmt.executeQuery();
		CachedRowSet allGraded = new CachedRowSetImpl();
		allGraded.populate(rs);
		
		return allGraded;
	}
	
	public static CachedRowSet getApplicationsByReviewer (int reviewer) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM public.review_applications WHERE review_applications.reviewer = ?;");
		stmt.setInt(1, reviewer);
		
		ResultSet rs = stmt.executeQuery();
		CachedRowSet allGraded = new CachedRowSetImpl();
		allGraded.populate(rs);
		
		return allGraded;
	}
	
	public static CachedRowSet getGradedByReviewer(int reviewer) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM public.review_applications WHERE review_applications.grade > 0 AND review_applications.reviewer = ?;");
		stmt.setInt(1, reviewer);
		
		ResultSet rs = stmt.executeQuery();
		CachedRowSet allGraded = new CachedRowSetImpl();
		allGraded.populate(rs);
		
		return allGraded;
	}
	
	public static CachedRowSet getNotGradedByReviewer(int reviewer) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT * FROM public.review_applications WHERE review_applications.grade < 0 AND review_applications.reviewer = ?;");
		stmt.setInt(1, reviewer);
		
		ResultSet rs = stmt.executeQuery();
		CachedRowSet allGraded = new CachedRowSetImpl();
		allGraded.populate(rs);
		
		return allGraded;
	}
	
	public static int getAverageGrade(int id) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT review_applications.grade FROM public.review_applications WHERE review_applications.id = ?;");
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		
		int i = 0;
		int average = 0;
		while(rs.next()){
			average += rs.getInt(1);
			i++;
		}
		return average/i;
	}
	
	public static boolean updateAppStatus(int id, String decision) throws ClassNotFoundException, SQLException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("UPDATE app_status SET status=? WHERE id = ?;");
		stmt.setString(1, decision);
		stmt.setInt(2, id);
		stmt.execute();
		
		return true;
	}

	public static boolean insertReview(App app) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("INSERT INTO review_applications( id, comments, grade, reviewer) VALUES (?, ?, ?, ?);");
		stmt.setInt(1, app.getUserId());
		stmt.setString(2, app.getComments());
		stmt.setInt(3, app.getAverageGrade());
		stmt.setInt(4, app.getReviewer());
		
		try{
		stmt.execute();
		}
		catch(SQLException e){
			stmt = conn.prepareStatement("UPDATE review_applications SET comments=?, grade=? WHERE id = ? AND reviewer = ?;");
			stmt.setString(1, app.getComments());
			stmt.setInt(2, app.getAverageGrade());
			stmt.setInt(3, app.getUserId());
			stmt.setInt(4, app.getReviewer());
			stmt.execute();
		}
		return true;
	}
	
	public static Vector<String> getAllComments(int id) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT review_applications.comments FROM public.review_applications WHERE review_applications.id = ?;");
		stmt.setInt(1, id);
		
		ResultSet rs = stmt.executeQuery();
		
		Vector<String> comments = new Vector<String>();
		rs.next();
		if(rs.getString(1)!= null){
			comments.add(rs.getString(1));
			while(rs.next()){
				comments.add(rs.getString(1));
			}
		}else{
			comments.add(null);
		}
		
		return comments;
		
	}
	
	public static Vector<Integer> getAllGrades(int id) throws SQLException, ClassNotFoundException{
		Class.forName("org.postgresql.Driver");

		String database = "postgres";
		String username = "postgres";
		String password = "postgres";
		
		Connection conn = DriverManager.getConnection("jdbs:postgresql:" + database, username, password);
				
		PreparedStatement stmt = conn.prepareStatement("SELECT review_applications.grade FROM public.review_applications WHERE review_applications.id = ?;");
		stmt.setInt(1, id);
		
		ResultSet rs = stmt.executeQuery();
		
		Vector<Integer> grades = new Vector<Integer>();
		rs.next();
		if(rs.getInt(1)!= -1){
			grades.add(rs.getInt(1));
			while(rs.next()){
				grades.add(rs.getInt(1));
			}
		}else{
			grades.add(-1);
		}
		
		return grades;
		
	}
	
}
